import xlrd
import os


class XlsTool:
    def __init__(self, filepath):
        book = xlrd.open_workbook(filepath)
        self.sheet1 = book.sheets()[0]

    def get_part1(self):
        custom_company_number = self.sheet1.cell(3, 1).value
        custom_company = self.sheet1.cell(3, 3).value
        custom_name = self.sheet1.cell(4, 1).value
        order_id = self.sheet1.cell(5, 3).value
        order_date = self.sheet1.cell(4, 3).value
        custom_name_number = self.sheet1.cell(7, 1).value
        custom_phone = str(int(self.sheet1.cell(5, 1).value))
        item = {
            "custom_company_number": custom_company_number,
            "custom_company": custom_company,
            "custom_name": custom_name,
            "order_id": order_id,
            "order_date": order_date,
            "custom_name_number": custom_name_number,
            "custom_phone": custom_phone,
        }
        return item

    def get_part2(self):
        products = []
        nrows = self.sheet1.nrows
        product_number_start = 11
        inv = nrows - product_number_start - 4
        for i in range(inv):
            row = product_number_start + i
            product_number = self.sheet1.cell(row, 0).value
            product_name = self.sheet1.cell(row, 1).value
            product_sum = self.sheet1.cell(row, 3).value
            if product_sum:
                try:
                    product_sum = product_sum[0]
                    product_sum = int(product_sum)
                except Exception as e:
                    raise e
            else:
                product_sum = 0
            if product_number:
                products.append({
                    "product_number": product_number,
                    "product_name": product_name,
                    "product_sum": product_sum
                })
        return products

    def get_all(self):
        part1 = self.get_part1()
        part2 = self.get_part2()
        part1.update({"products": part2})
        return part1
